Imports System
Imports System.Collections.Generic
Imports System.Windows.Controls
Imports System.Windows.Data
Imports System.Windows.Input
Imports System.Windows.Media
Imports System.Windows.Media.Animation
Imports System.Windows.Media.Imaging
Imports System.Windows.Navigation
Imports System.Windows.Shapes
Imports OMANE.Common.UI
Imports ICSharpCode.AvalonEdit.Highlighting
Imports System.CodeDom.Compiler
Imports System.Linq.Expressions
Imports System.Data
Imports OMANE.Modules.Builder.MSFunctions
Imports OMANE.Modules.Builder.MSExpression
Imports ICSharpCode.AvalonEdit.CodeCompletion
Imports System.ComponentModel


Public Class MSOperation
    Inherits Dynamic.DynamicObject

    Private data As New Hashtable()
    Private tridata As New List(Of MSExpression)
    Private filtredata As MSExpression
    Private groupbydata As New List(Of MSExpression)
    Private table As String = "FROM TABLE"
    Private takedata As MSExpression

    Dim _Universe As MSUniverse

    Sub New(ByVal universe As MSUniverse)
        Me._Universe = universe
    End Sub

    Public Sub UpdateTableStr()
        Dim r = "FROM " & _Universe.MainTable.Name
        Dim joinstrs = New List(Of String)
        Dim simplestr = New List(Of String)

        Dim mstables As New List(Of MSTable)
        For Each expr As MSExpression In data.Values
            For Each mt In expr.Sources
                If Not mstables.Contains(mt) Then
                    mstables.Add(mt)
                End If
            Next
        Next

        mstables.Remove(_Universe.MainTable)
        Dim qo = From mt In mstables Where mt.Param IsNot Nothing Select mt.Param

        For Each p In qo
            If p.Properties.Count > 0 Then
                Dim q = From e In p.Properties
                        Select _Universe.MainTable.Name & "." & e.Name &
                                " = " & _
                                p.Table.Name & "." & e.Name

                joinstrs.Add(" INNER JOIN " & p.Table.Name & " ON " & String.Join(" AND ", q.ToArray()))
            Else
                simplestr.Add(p.Table.Name)
            End If
        Next
        table = r & String.Join(", ", joinstrs.ToArray())
        If simplestr.Count > 0 Then
            table = table & "," & String.Join(", ", simplestr.ToArray())
        End If
    End Sub


    Public Function GetSql() As String
        UpdateTableStr()

        Dim filtrestr = ""
        If filtredata IsNot Nothing Then
            filtrestr = "WHERE " & filtredata.ToString()
        End If
        Dim tristr = ""
        If tridata.Count > 0 Then
            Dim q = tridata.Select(Function(e) e.ToString()).ToArray()
            tristr = "ORDER BY " & String.Join(", ", q)
        End If
        Dim colsstr = "*"
        If data.Count > 0 Then
            Dim datastr = New List(Of String)
            For Each k In data.Keys
                datastr.Add(data(k).ToString() & " AS " & k.ToString())
            Next
            colsstr = "SELECT " & _
                      If(takedata Is Nothing, "", takedata.ToString()) _
                      & String.Join(", ", datastr.ToArray())
        End If
        Dim gbstr = ""
        If groupbydata.Count > 0 Then
            Dim q = groupbydata.Select(Function(e) e.ToString()).ToArray()
            gbstr = "GROUP BY " & String.Join(", ", q)
        End If
        Return colsstr & vbCrLf & table & vbCrLf & filtrestr & vbCrLf & tristr & vbCrLf & gbstr
    End Function

    Public Overrides Function TryConvert(ByVal binder As System.Dynamic.ConvertBinder, ByRef result As Object) As Boolean
        Return MyBase.TryConvert(binder, result)
    End Function

    Public Overrides Function TryInvokeMember(ByVal binder As System.Dynamic.InvokeMemberBinder, ByVal args() As Object, ByRef result As Object) As Boolean
        Return MyBase.TryInvokeMember(binder, args, result)
    End Function

    'Public Shared Widening Operator CType(ByVal s As MSOperation) As Double

    'End Operator
    'Public Shared Widening Operator CType(ByVal s As MSOperation) As Boolean

    'End Operator
    'Public Shared Widening Operator CType(ByVal s As MSOperation) As Integer

    'End Operator
    'Public Shared Widening Operator CType(ByVal s As MSOperation) As TimeSpan

    'End Operator
    'Public Shared Widening Operator CType(ByVal s As MSOperation) As DateTime

    'End Operator
    'Public Shared Widening Operator CType(ByVal s As MSOperation) As Vector

    'End Operator
    'Public Shared Widening Operator CType(ByVal s As MSOperation) As Matrix

    'End Operator
    Public Function GetDataAsList(ByVal s As MSOperation) As List
        Dim cmd = s._Universe.Connection.CreateCommand()
        cmd.CommandText = s.GetSql()
        cmd.CommandType = CommandType.Text

        cmd.Connection.Open()
        Dim reader = cmd.ExecuteReader()
        cmd.Connection.Close()

        'If reader.FieldCount = 0 Then
        '    Return New List()
        'End If

        'Dim storage(reader.FieldCount) As Object

        ''While reader.Read()
        ''    reader.GetValues(storage)

        ''End While
        'cmd.Connection.Close()
    End Function
    Public Function GetDataAsDataTable(ByVal s As MSOperation) As DataTable
        Dim dt = New DataTable()
        Dim cmd = s._Universe.Connection.CreateCommand()
        cmd.CommandText = s.GetSql()
        cmd.CommandType = CommandType.Text

        If cmd.Connection.State = ConnectionState.Open Then
            cmd.Connection.Close()
        End If

        cmd.Connection.Open()
        Dim reader = cmd.ExecuteReader()


        If reader.FieldCount = 0 Then
            Return dt
        End If

        For i As Integer = 0 To reader.FieldCount - 1
            Dim c = New DataColumn(reader.GetName(i), reader.GetFieldType(i))
            dt.Columns.Add(c)
        Next

        While reader.Read()
            Dim r = dt.NewRow()
            For i As Integer = 0 To reader.FieldCount - 1
                r(i) = reader.GetValue(i)
            Next
            dt.Rows.Add(r)
        End While
        cmd.Connection.Close()

        reader.Dispose()
        cmd.Dispose()
        reader = Nothing
        cmd = Nothing

        Return dt
    End Function

    Public Sub OrderBy(ByVal Expr As MSExpression, Optional ByVal Descending As Boolean = False)
        Dim result As New MSExpression
        result.Parameters = New Func(Of String)() {Expr.ResultFunctStr}

        If Descending Then
            result.FuncStr = "{0} DESC"
        Else
            result.FuncStr = "{0} ASC"
        End If
        tridata.Add(result)
    End Sub
    Public Sub Where(ByVal Expr As MSExpression)
        Me.filtredata = Expr
    End Sub
    Public Sub GroupBy(ByVal expr As MSExpression)
        groupbydata.Add(expr)
    End Sub
    Public Sub GroupBy(ByVal ParamArray expr() As MSExpression)
        groupbydata.AddRange(expr)

    End Sub
    Public Function GetData() As Object
        Return GetDataAsDataTable(Me)
    End Function
    Public Function GetData(Of T)() As T

    End Function
    Public Sub Take(ByVal count As MSExpression, Optional ByVal percent As Boolean = False)
        Dim result As New MSExpression
        result.Parameters = New Func(Of String)() {count.ResultFunctStr}

        If percent Then
            result.FuncStr = "TOP {0} PERCENT"
        Else
            result.FuncStr = "TOP {0} "
        End If
        takedata = result
    End Sub
    Public Sub Skip(ByVal count As MSExpression)

    End Sub
    Public Sub SkipAndTake(ByVal skipcount As MSExpression, ByVal takecount As MSExpression)

    End Sub

    Public Overrides Function TrySetMember(ByVal binder As System.Dynamic.SetMemberBinder, ByVal value As Object) As Boolean
        Dim expr = TryCast(value, MSExpression)

        If expr Is Nothing Then
            Dim prop = TryCast(value, MSProperty)
            If expr Is Nothing Then Return False
            data.Add(binder.Name, prop)
            Return True
        Else
            data.Add(binder.Name, expr)
            Return True
        End If
        Return MyBase.TrySetMember(binder, value)
    End Function
    Public Overrides Function TryGetMember(ByVal binder As System.Dynamic.GetMemberBinder, ByRef result As Object) As Boolean
        If data.ContainsKey(binder.Name) Then
            result = data(binder.Name)
            Return True
        End If
        Return MyBase.TryGetMember(binder, result)
    End Function
End Class
